#coding=utf-8
import urllib2
import cookielib
from BeautifulSoup import BeautifulSoup,SoupStrainer
import MySQLdb
import time
import pyodbc

#检查数据库的创建情况
class MyDatabaseTmall(object):
    def __init__(self):
        self.conn=conn=conn=MySQLdb.connect(host='115.238.55.68',user='root',passwd='123,abc',db='tmall',port=3306,charset='utf8' )
        self.cursor = conn.cursor()
    def runsql(self,sql):
        print sql
        self.cursor.execute(sql.decode('utf-8').encode('gbk'))
        self.conn.commit()
        
    def fetchdata(self,sql):
        self.cursor.execute(sql)
        ituple=self.cursor.fetchall()
        mylist=list()
        for i in ituple:
            mylist.append(i[0])
        return mylist
        
    
class EverydaySaleData(object):
    def __init__(self):
        self.id=''
        self.rundate=''
        self.price=''
        self.salenum=''
    def insertSql(self):
        s='if not exists(select * from everydaysaleDatas where id='+self.id+" and rundate='"+self.rundate+"') begin insert into everydaysaleDatas(id,rundate,price,salenum) values("
        s+="'"+self.id+"',"
        s+="'"+self.rundate+"',"
        s+=str(self.price)+","
        s+="'"+self.salenum+"') end"
        print s
        return s
        
class EverydaySaleDatas(object):
    def __init__(self):
        self.mylist=[]
        self.count=0
        
    def add(self,everydaySaleData):
        self.mylist.append(everydaySaleData)
        self.count+=1
        print everydaySaleData.id
        if self.count==60:
            self.sendtoDatabase()
            self.count=0
            self.mylist=[]
    
    def sendtoDatabase(self):
        x=MyDatabaseTmall()
        for i in self.mylist:
            x.runsql(i.insertSql())
        
        
        
        
class Commodity(object):
    def __init__(self):
        self.id=''
        self.fromwhere=''
        self.name=''
        self.href=''
        self.shop=''
        self.cate=''
        
    def addFromRefdata(self,jDict=None):
        self.brand=''
        self.goodstype=''
        self.usecycle=''
        self.approvaldoc=''
        self.diameter=''
        self.thickness=''
        self.containwater=''
        self.placeoforigin=''
        self.package=''
        self.colortype=''
        self.specifications=''
        self.capacity=''
        self.unitofquantity=''
        
        def getempty(x):
            y=''
            if x:
                y=x
            else:
                pass
            return y
        
        for j in jDict:
            self.brand=getempty(jDict.get('品牌'))
            self.goodstype=getempty(jDict.get('型号'))
            self.usecycle=getempty(jDict.get('佩戴周期'))#佩戴周期
            self.approvaldoc=getempty(jDict.get('批准文号'))#批准文号
            self.diameter=getempty(jDict.get('直径'))
            self.thickness=getempty(jDict.get('厚度'))
            self.containwater=getempty(jDict.get('含水'))
            self.placeoforigin=getempty(jDict.get('产地'))#产地
            self.package=getempty(jDict.get('包装规格'))#包装规格
            self.colortype=getempty(jDict.get('颜色分类'))#颜色分类
            self.specifications=getempty(jDict.get('规格'))
            self.capacity=getempty(jDict.get('容量'))#容量
        for i in (self.package,self.specifications,self.capacity):
            if i!='':
                self.unitofquantity=i
        s='update commodities set '
        s+="brand='"+self.brand+"',"
        s+="goodstype='"+self.goodstype+"',"
        s+="usecycle='"+self.usecycle+"',"
        s+="approvaldoc='"+self.approvaldoc+"',"
        s+="diameter='"+self.diameter+"',"
        s+="thickness='"+self.thickness+"',"
        s+="containwater='"+self.containwater+"',"
        s+="placeoforigin='"+self.placeoforigin+"',"
        s+="package='"+self.package+"',"
        s+="colortype='"+self.colortype+"',"
        s+="specifications='"+self.specifications+"',"
        s+="capacity='"+self.capacity+"' ,"
        s+="unitofquantity='"+self.unitofquantity+"' "
        s+="where id='"+self.id+"'"
        x=MyDatabaseTmall()
        x.runsql(s)
           
    def insertSql(self):
        s='if not exists(select * from commodities where id='+self.id+') begin insert into commodities(id,fromwhere,name,href,shop,cate) values('
        s+="'"+self.id+"',"
        s+="'"+self.fromwhere+"',"
        s+="'"+self.name+"',"
        s+="'"+self.href+"',"
        s+="'"+self.shop+"',"
        s+="'"+self.cate+"') end"
        print s
        return s
        
class Commodities(object):
    def __init__(self):
        self.mylist=[]
        self.count=0
        
    def add(self,commodity):
        self.mylist.append(commodity)
        self.count+=1
        print commodity.id
        if self.count==60:
            self.sendtoDatabase()
            self.count=0
            self.mylist=[]
        
    def sendtoDatabase(self):
        x=MyDatabaseTmall()
        for i in self.mylist:
            x.runsql(i.insertSql())
        
class ClimbTmallFromCate(object):
    def __init__(self,cururl):
        self.todaystr=time.strftime('%Y-%m-%d', time.localtime())#运行开始时间
        menudict=self.__getmenu(cururl)
        self.commodities=Commodities()#临时产品表
        self.everydaySaleDatas=EverydaySaleDatas()#临时销售数据表
        self.__getgoodsfrommenu(menudict)
        
        
    def __getmenu(self,cururl):#ok
        tmenudict={}
        cj = cookielib.CookieJar()
        opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
        response_main=opener.open(urllib2.Request(cururl))
        content=response_main.read().decode('gbk').encode('utf-8')
        soup = BeautifulSoup(content)
        turl='http://list.tmall.com/search_product.htm'
        #need repair
        pass
        #need repair
        tmall_menulist=soup.find('div',{'class':'j_Cate attr'}).findAll('a',{'data-f':'spu-cat','data-c':'cat'})
        for temp_menu in tmall_menulist:
            menu_url=str(turl+temp_menu['href'])[:-9]
            menu_name=temp_menu.b.contents[0]#.decode('utf-8')
            tmenudict[menu_name]=menu_url
        return tmenudict

    def __getgoodsfrommenu(self,mydict):
        for cate,url in mydict.items():
            self.__getgoodsfromeachurl(cate,url)
            
    def __getgoodsfromeachurl(self,cate,url):
        urlnext=url
        cj = cookielib.CookieJar()
        opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
        while urlnext:
            print urlnext
            response_catepage=opener.open(urllib2.Request(urlnext))
            content_catepage=response_catepage.read().decode('gbk').encode('utf-8')
            #print content_catepage
            soup_detail =BeautifulSoup(content_catepage)
            self.__getgoodsitems(soup_detail,cate)
            #try to find next page,if not exist,return None!
            try:
                urlnext=soup_detail.find('a',{'class':'ui-page-next'})['href'][:-9]
                urlnext='http://list.tmall.com/search_product.htm'+urlnext
            except:
                urlnext=None

    def __getgoodsitems(self,soup,cate): 
        for detailitem in soup.find('div',{'class':'view  grid-34  clearfix'}).findAll('div',{'class':'product'}):
            #print 'ok'
            commodity=Commodity()
            everydaySaleData=EverydaySaleData()
            commodity.fromwhere='tmall'
            commodity.id=detailitem['data-id']
            commodity.name=detailitem.find('p',{'class':'productTitle'}).a['title']
            commodity.href='http://detail.tmall.com/item.htm?id='+commodity.id
            commodity.shop=detailitem.find('p',{'class':'productStatus'}).find('span',{'data-icon':'small'})['data-nick']
            commodity.cate=cate
            
            everydaySaleData.id=commodity.id
            everydaySaleData.rundate=self.todaystr
            everydaySaleData.price=detailitem.find('p',{'class':'productPrice'}).em['title']
            everydaySaleData.salenum=detailitem.find('p',{'class':'productStatus'}).span.em.contents[0][:-1]

            self.commodities.add(commodity)
            self.everydaySaleDatas.add(everydaySaleData)

class ClimbTmallFromID(object):
    def __init__(self):
        x=MyDatabaseTmall()
        sql="select id from commodities where commodities.brand is null"
        ilist=x.fetchdata(sql)
        self.commodities=Commodities()
        self.cj = cookielib.CookieJar()
        self.opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(self.cj))
        self.__getdata_fromidlist(ilist)
        
    def __getdata_fromidlist(self,idlist):
        for i in idlist:
            self.__getdata_fromid(i)
            
    def __getdata_fromid(self,id):
        commodity=Commodity()
        commodity.id=id
        cururl='http://detail.tmall.com/item.htm?id='+id
        #try:
        res=None
        while True:
            res=self.opener.open(cururl)
            contents=res.read().decode('gbk').encode('utf-8')
            if'g' in contents:break
            print 'ok',id
        mydict={}
        #contents=res.read().decode('gbk').encode('utf-8')
        #print contents
        soup=BeautifulSoup(contents)
        try:
            soup_attrul=soup.find('ul',{'id':'J_AttrUL'})
            #print id
            for i in soup_attrul.findAll('li'):
                mydict[((str(i.contents[0]).replace('&nbsp;', '')).split(u':')[0]).encode('utf-8')]=str(i['title']).replace('&nbsp;', '')
            #print mydict.get(u'品牌')
            commodity.addFromRefdata(mydict)
        except Exception,e:
            pass
        #except Exception,e:
            #pass
        
if __name__=='__main__':
    url='http://list.tmall.com/search_product.htm?active=1&from=sn_1_cat&area_code=330100&search_condition=7&style=g&sort=s&n=60&s=0&cat=50074933'#J_crumbs
    t=ClimbTmallFromCate(url)
    x=ClimbTmallFromID()